use master
go

create database eproject1
go

use eproject1
go


create table [role]
(
role_id int not null,
roles nvarchar(50) not null,
constraint pk_role_id primary key (role_id)
)
go

create table account
(
account_id int identity(1,1) not null,
role_id int not null,
account nvarchar(50) not null,
[password] nvarchar(50) not null,
constraint pk_account_id primary key (account_id),
constraint fk_role_id foreign key (role_id) references [role](role_id)
)
go

create table type_puzzle
(
type_puzzle_id int not null,
[type] nvarchar(100) not null,
constraint pk_type_puzzle_id primary key (type_puzzle_id)
)
go

create table question
(
question_id int not null,
question_content nvarchar(200) not null,
constraint pk_question_id primary key (question_id)
)
go

create table answer
(
question_id int not null,
answer_content nvarchar(50) not null,
type_puzzle_id int not null,
constraint pk_question_id_answer primary key (question_id),
constraint fk_question_id_answer foreign key (question_id) references question(question_id),
constraint fk_type_puzzle_id foreign key (type_puzzle_id) references type_puzzle(type_puzzle_id)
)
go

create table puzzles
(
puzzle_id int not null,
type_puzzle_id int not null,
timer int not null,
constraint pk_puzzle_id primary key (puzzle_id),
constraint fk_type_puzzle_id_puzz foreign key (type_puzzle_id) references type_puzzle(type_puzzle_id)
)
go

create table highscores
(
[user_id] int identity(1,1) not null,
[user_name] nvarchar(50) not null,
scores int not null,
timer int not null,
type_puzzle_id int not null,
constraint pk_user_id primary key ([user_id]),
constraint fk_type_puzzle_id_high foreign key (type_puzzle_id) references type_puzzle(type_puzzle_id)
)
go

create table [rows]
(
row_id int not null,
puzzle_id int not null,
row_content nvarchar(50) not null,
question_id int not null,
constraint pk_puzzle_row primary key (puzzle_id,row_id),
constraint fk_puzzle_row foreign key (question_id) references puzzles(puzzle_id),
constraint fk_question_row foreign key (question_id) references question(question_id)
)
go

create table [columns]
(
column_id int not null,
puzzle_id int not null,
column_content nvarchar(50) not null,
question_id int not null,
constraint pk_puzzle_col primary key(puzzle_id,column_id),
constraint fk_puzzle_col foreign key (question_id) references puzzles(puzzle_id),
constraint fk_question_col foreign key (question_id) references question(question_id)
)
go


drop database eproject1
drop table [rows]
drop table [columns]
